﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using nwrole.toolbox.sync.entity;

namespace nwrole.toolbox.sync
{
    public class SqlBuilder
    {
        //public const string parameterPrefix = ":";
        public const string parameterPrefix = "@";
        
        #region column names helpers
        public string GetColumnNames(IEnumerable<SyncEntityColumn> cols, string prefix = "")
        {
            StringBuilder sb = new StringBuilder();
            int colCounter = 0;
            foreach (var column in cols)
            {
                if (colCounter > 0)
                {
                    sb.Append(",");
                }
                sb.Append(prefix);
                sb.Append("[");
                sb.Append(column.ColumnName);
                sb.Append("]");
                colCounter++;
            }
            return sb.ToString();
        }

        private string GetColumnDef(IEnumerable<SyncEntityColumn> cols)
        {
            StringBuilder sb = new StringBuilder();
            int colCounter = 0;
            foreach (var column in cols)
            {
                if (colCounter > 0)
                {
                    sb.Append(",");
                }

                if (GlobalSettings.IsOracle)
                    sb.Append(string.Format("[{0}] {1}", column.ColumnName, MakeOracleType(column.DestSqlTypeName, column.DestSqlTypeMaxLength, column.DestSqlTypeNullableBool)));
                else
                    sb.Append(string.Format("[{0}] {1}", column.ColumnName, MakeTsqlType(column)));
                colCounter++;
            }
            return sb.ToString();
        }
/*
        [Fastighetid] [bigint] NOT NULL,
	[Indelning] [nvarchar](60) NULL,
	[Indelningnr] [nvarchar](60) NULL,
	[Omrade] [nvarchar](60) NULL,
	[Omradenr] [nvarchar](60) NULL";
        */

        private string MakeOracleType(string TypeName, int MaxLength, bool TypeNullable)
        {
            if (MaxLength > 0)
            {
                if (TypeName.ToLower().Contains("char"))
                {
                    TypeName = TypeName + "(" + MaxLength + ")";
                }
            }

            if (!TypeNullable)
            {
                return TypeName + " not null";
            }

            return TypeName;
        }

        //private string MakeTsqlType(string TypeName, int MaxLength, bool TypeNullable)
        private string MakeTsqlType(SyncEntityColumn column)
        {
            string MaxLengthStr = (column.DestSqlTypeMaxLength == -1) ? "MAX" : "" + column.DestSqlTypeMaxLength;
            string TypeName = column.DestSqlTypeName;
            if (column.DestSqlTypeMaxLength != 0)
            {
                if (TypeName.ToLower().Contains("char"))
                {
                    TypeName = TypeName + "(" + MaxLengthStr + ")";
                }
            }

            if (TypeName.ToLower().Contains("decimal"))
            {
                TypeName = string.Format("{0}({1},{2})", TypeName, column.DestSqlTypePrecision, column.DestSqlTypeScale);
            }

            if (!column.DestSqlTypeNullableBool)
            {
                return TypeName + " not null";
            }
            return TypeName;
        }

        private string GetGhostColumnDef(IEnumerable<SyncEntityColumn> cols)
        {
            StringBuilder sb = new StringBuilder();
            int colCounter = 0;
            foreach (var column in cols)
            {
                if (colCounter > 0)
                {
                    sb.Append(",\r\n");
                }
                if (GlobalSettings.IsOracle)
                    sb.Append(string.Format("[{0}] {1}", column.ColumnName, MakeOracleType(column.SourceSqlTypeName, column.SourceSqlTypeMaxLength, column.SourceSqlTypeNullableBool)));
                else
                    sb.Append(string.Format("[{0}] {1}", column.ColumnName, MakeTsqlType(column)));
                colCounter++;
            }
            return sb.ToString();
        }

        private string GetColumnNamesPrimaryKeyForWhere(IEnumerable<SyncEntityColumn> cols, string prefix1 = "", string prefix2 = parameterPrefix, string emptyReplacement = "")
        {
            StringBuilder sb = new StringBuilder();
            int colCounter = 0;
            foreach (var column in cols)
            {
                if (column.PrimaryKey)
                {
                    if (colCounter > 0)
                    {
                        sb.Append(", ");
                    }
                    sb.Append(prefix1);
                    sb.Append("[");
                    sb.Append(column.ColumnName);
                    sb.Append("]=");
                    sb.Append(prefix2);
                    sb.Append("[");
                    sb.Append(column.ColumnName);
                    sb.Append("]");
                    colCounter++;
                }
            }
            string result = sb.ToString();
            if (string.IsNullOrEmpty(result) && !string.IsNullOrEmpty(emptyReplacement))
                result = emptyReplacement;

            return result;
        }

        private string GetColumnNamesPrimaryKeyForWhere(IEnumerable<SyncEntityColumn> cols, string delimeter = ", ", string prefix1 = "", string prefix2 = parameterPrefix, string emptyReplacement = "") {
            StringBuilder sb = new StringBuilder();
            int colCounter = 0;
            foreach (var column in cols) {
                if (column.PrimaryKey) {
                    if (colCounter > 0) {
                        sb.Append(delimeter);
                    }
                    sb.Append(prefix1);
                    sb.Append("[");
                    sb.Append(column.ColumnName);
                    sb.Append("]=");
                    sb.Append(prefix2);
                    sb.Append("[");
                    sb.Append(column.ColumnName);
                    sb.Append("]");
                    colCounter++;
                }
            }
            string result = sb.ToString();
            if (string.IsNullOrEmpty(result) && !string.IsNullOrEmpty(emptyReplacement))
                result = emptyReplacement;

            return result;
        }

        private string GetColumnNamesForSet(IEnumerable<SyncEntityColumn> cols, string prefix1 = "", string prefix2 = parameterPrefix)
        {
            StringBuilder sb = new StringBuilder();
            int colCounter = 0;
            foreach (var column in cols)
            {
                if (!column.PrimaryKey)
                {
                    if (colCounter > 0)
                    {
                        sb.Append(", ");
                    }
                    sb.Append(prefix1);
                    sb.Append("[");
                    sb.Append(column.ColumnName);
                    sb.Append("]=");
                    sb.Append(prefix2);
                    sb.Append("[");
                    sb.Append(column.ColumnName);
                    sb.Append("]");
                    colCounter++;
                }
            }
            return sb.ToString();
        }

        #endregion

        public string SelectAll(string columns, string table)
        {
/*            StringBuilder sb = new StringBuilder();
            string start = "";
            foreach (string s in columns) 
            {
                sb.Append(start);
                sb.Append(s);
                start = ",";
            }
            string cols = sb.ToString(); */
            string sql = String.Format("select {0} from {1} order by {2}",
                columns, table, columns
            );
            return sql;
        }

        public string SelectAll(SyncEntity map)
        {
            string sql = String.Format("select {0} from {1} where rownum <= 2",
                GetColumnNames(map.SyncEnabledColumns),
                map.SourceTableName
            );
            return sql;
        }

        public string DeleteOldRowsFromDestinationTable(SyncEntity map)
        {
            DateTime deleteDat = DateTime.Now.AddDays(-map.KeepDays);

            string sql = String.Format("delete {0} " + 
                        "from {0} x " +
                        "inner join int_message on int_message.id = x.MessageId " +
                        "where int_message.regdat < '{1}'",
                map.DestinationTableName,
                deleteDat.ToString("yyyy-MM-dd")
            );
            return sql;
        }

        public string DeleteFromShadowTable(SyncEntity map)
        {
            string sql = String.Format("delete from {0}",
                map.ShadowTableName
            );
            return sql;
        }

        public string SelectIntoShadowTable(SyncEntity map)
        {
            string sql = String.Format("insert into {0} ({1}) select {2} from {3}",
                map.ShadowTableName,
                GetColumnNames(map.SyncEnabledColumns),
                GetColumnNames(map.SyncEnabledColumns),
                map.SourceTableName
            );
            return sql;
        }

        // http://psoug.org/reference/merge.html

/*        merge into jfh_bo.objektsbundet_copy mm
using (
select OBJEKTSBUNDETID, HYRESDELID, OBJEKTID, FROMDAT, TOMDAT from wf.objektsbundet
minus
select OBJEKTSBUNDETID, HYRESDELID, OBJEKTID, FROMDAT, TOMDAT from jfh_bo.objektsbundet_copy
) ss
ON (mm.OBJEKTSBUNDETID = ss.OBJEKTSBUNDETID)
WHEN MATCHED THEN
UPDATE SET mm.FROMDAT=ss.FROMDAT,mm.TOMDAT=ss.TOMDAT, mm.OBJEKTID=ss.OBJEKTID, mm.HYRESDELID=ss.HYRESDELID  
WHEN NOT MATCHED THEN
INSERT (mm.OBJEKTSBUNDETID, mm.FROMDAT, mm.TOMDAT, mm.OBJEKTID, mm.HYRESDELID)
VALUES (ss.OBJEKTSBUNDETID, ss.FROMDAT, ss.TOMDAT, ss.OBJEKTID, ss.HYRESDELID)
        */


        public string DirectUpsertShadowTable(SyncEntity map) {
            if (GlobalSettings.IsOracle)
                return DirectUpsertOracleShadowTable(map);

            return DirectUpsertSQLShadowTable(map);
        }

        private string DirectUpsertOracleShadowTable(SyncEntity map)
        {
            string sql = String.Format(@"
    merge into {0} mm
    using (
    select {1} from {2}
    minus
    select {3} from {4}
    ) ss
    ON ({5})
    WHEN MATCHED THEN
    UPDATE SET {6}, mm.SYNCDAT=" + GlobalSettings.SqlVariablePrefix + @"SYNCDAT1
    WHEN NOT MATCHED THEN
    INSERT ({7},mm.SYNCDAT)
    VALUES ({8}," + GlobalSettings.SqlVariablePrefix + @"SYNCDAT2)",

                map.ShadowTableName, // {0}
                GetColumnNames(map.SyncEnabledColumns).Replace("TEXT_CLOB", "dbms_lob.substr(TEXT_CLOB, 4000,1) as TEXT_CLOB"), // {1}
                map.SourceTableName, // {2}
                GetColumnNames(map.SyncEnabledColumns), // {3}
                map.ShadowTableName, // {4}
                GetColumnNamesPrimaryKeyForWhere(map.SyncEnabledColumns, "mm.", "ss.", "1=1"), // {5}
                GetColumnNamesForSet(map.SyncEnabledColumns, "mm.", "ss."), // {6}
                GetColumnNames(map.SyncEnabledColumns, "mm."), // {7}
                GetColumnNames(map.SyncEnabledColumns, "ss.") // {8}
            );
            // return sql.Replace("\r", "").Replace("\n", "");
            return sql.Replace("\n", "");
        }

        private string DirectUpsertSQLShadowTable(SyncEntity map) {
            string sql = String.Format(@"
    merge into {0} mm
    using (
    select {1} from {2}
    EXCEPT
    select {3} from {4}
    ) ss
    ON ({5})
    WHEN MATCHED THEN
    UPDATE SET {6}, SYNCDAT=" + GlobalSettings.SqlVariablePrefix + @"SYNCDAT1
    WHEN NOT MATCHED THEN
    INSERT ({7},SYNCDAT)
    VALUES ({8}," + GlobalSettings.SqlVariablePrefix + @"SYNCDAT2);",

                map.ShadowTableName, // {0}
                GetColumnNames(map.SyncEnabledColumns).Replace("TEXT_CLOB", "dbms_lob.substr(TEXT_CLOB, 4000,1) as TEXT_CLOB"), // {1}
                map.SourceTableName, // {2}
                GetColumnNames(map.SyncEnabledColumns), // {3}
                map.ShadowTableName, // {4}
                GetColumnNamesPrimaryKeyForWhere(map.SyncEnabledColumns, " AND ", "mm.", "ss.", "1=1"), // {5}
                GetColumnNamesForSet(map.SyncEnabledColumns, "", "ss."), // {6}
                GetColumnNames(map.SyncEnabledColumns), // {7}
                GetColumnNames(map.SyncEnabledColumns, "ss.") // {8}
            );
            // return sql.Replace("\r", "").Replace("\n", "");
            return sql.Replace("\n", "");
        }

        public string SelectFromShadowTableBySYNCDAT(SyncEntity map)
        {
            string sql = String.Format("select {0} from {1} where SYNCDAT = " + GlobalSettings.SqlVariablePrefix + "SYNCDAT",
                GetColumnNames(map.SyncEnabledColumns),
                map.ShadowTableName
            );
            return sql;
        }

        public string SelectFromSourceTableBySYNCDAT(SyncEntity map, string extraFilterStr, string overrideFilterStr)
        {
            string sql = null;
            if (map.ResyncAll == 1)
            {
                if (!string.IsNullOrEmpty(extraFilterStr))
                {
                    extraFilterStr = " where (" + extraFilterStr + ")";
                    if (!string.IsNullOrEmpty(overrideFilterStr))
                        extraFilterStr = extraFilterStr + " OR (" + overrideFilterStr + ")";
                }
                else
                {
                    if (!string.IsNullOrEmpty(overrideFilterStr))
                        extraFilterStr = " where " + overrideFilterStr;
                    else
                        extraFilterStr = "";
                }

                sql = String.Format("select {0} from {1} {2}",
                    GetColumnNames(map.SyncEnabledColumns),
                       map.SourceTableName, extraFilterStr);
            }
            else
            {
                if (!string.IsNullOrEmpty(extraFilterStr))
                    extraFilterStr = " and " + extraFilterStr;
                else
                    extraFilterStr = "";

                if (!string.IsNullOrEmpty(overrideFilterStr))
                    overrideFilterStr = " OR (" + overrideFilterStr + ")";
                else
                    overrideFilterStr = "";
                 
                string paramName = GlobalSettings.SqlVariablePrefix + "SYNCDAT";
                if(!string.IsNullOrEmpty(map.SourceInsertedColumnName))
                {
                    sql = String.Format("select {0} from {1} where ((([{2}] > {3}) or ([{4}] is null and [{5}] > {6})) {7}) {8}",
                        GetColumnNames(map.SyncEnabledColumns), // {0}
                        map.SourceTableName,                    // {1}
                        map.SourceUpdatedColumnName,            // {2}
                        paramName,                              // {3}
                        map.SourceUpdatedColumnName,            // {4}
                        map.SourceInsertedColumnName,           // {5}
                        paramName,                              // {6}
                        extraFilterStr,                         // {7}
                        overrideFilterStr                       // {8}
                    );
                }
                else
                {
                    sql = String.Format("select {0} from {1} where (([{4}] is not null and [{2}] > {3}) {5}) {6}",
                        GetColumnNames(map.SyncEnabledColumns), // {0}
                        map.SourceTableName,                    // {1}
                        map.SourceUpdatedColumnName,            // {2}
                        paramName,                              // {3}
                        map.SourceUpdatedColumnName,            // {4}
                        extraFilterStr,                         // {5}
                        overrideFilterStr                       // {6}
                    );
                }
                
            }
            return sql;
        }

        public string MaxTimestampFromSourceTableByTimestamp(SyncEntity map, string extraFilterStr, string overrideFilterStr)
        {
            string sql = null;
            if (map.ResyncAll == 1 || map.HighestTimestamp == null || map.HighestTimestamp.Length == 0)
            {
                if (!string.IsNullOrEmpty(extraFilterStr))
                {
                    extraFilterStr = " where (" + extraFilterStr + ")";
                    if (!string.IsNullOrEmpty(overrideFilterStr))
                        extraFilterStr = extraFilterStr + " OR (" + overrideFilterStr + ")";
                }
                else
                {
                    if (!string.IsNullOrEmpty(overrideFilterStr))
                        extraFilterStr = " where " + overrideFilterStr;
                    else
                        extraFilterStr = "";
                }

                sql = String.Format("select MAX([{0}]) from {1} {2}",
                       map.SourceTimestampColumnName,
                       map.SourceTableName, extraFilterStr);
            }
            else
            {
                if (!string.IsNullOrEmpty(extraFilterStr))
                    extraFilterStr = " and " + extraFilterStr;
                else
                    extraFilterStr = "";

                if (!string.IsNullOrEmpty(overrideFilterStr))
                    overrideFilterStr = " OR (" + overrideFilterStr + ")";
                else
                    overrideFilterStr = "";

                string paramName = GlobalSettings.SqlVariablePrefix + "HighestTimestamp";

                sql = String.Format("select MAX([{0}]) from {1} where (([{4}] is not null and [{2}] > {3}) {5}) {6}",
                        map.SourceTimestampColumnName, // {0}
                        map.SourceTableName,                    // {1}
                        map.SourceTimestampColumnName,            // {2}
                        paramName,                              // {3}
                        map.SourceTimestampColumnName,            // {4}
                        extraFilterStr,                          // {5}
                        overrideFilterStr                           // {6}
                    );

            }
            return sql;
        }

        public string SelectFromSourceTableByTimestamp(SyncEntity map, string extraFilterStr, string overrideFilterStr)
        {
            string sql = null;
            if (map.ResyncAll == 1 || map.HighestTimestamp == null || map.HighestTimestamp.Length == 0)
            {
                if (!string.IsNullOrEmpty(extraFilterStr))
                {
                    extraFilterStr = " where (" + extraFilterStr + ")";
                    if(!string.IsNullOrEmpty(overrideFilterStr))
                        extraFilterStr = extraFilterStr + " OR (" + overrideFilterStr + ")";
                }
                else
                {
                    if (!string.IsNullOrEmpty(overrideFilterStr))
                        extraFilterStr = " where " + overrideFilterStr;
                    else
                        extraFilterStr = "";
                }
                
                sql = String.Format("select {0} from {1} {2}",
                    GetColumnNames(map.SyncEnabledColumns),
                       map.SourceTableName, extraFilterStr);
            }
            else
            {
                if (!string.IsNullOrEmpty(extraFilterStr))
                    extraFilterStr = " and " + extraFilterStr;
                else
                    extraFilterStr = "";

                if (!string.IsNullOrEmpty(overrideFilterStr))
                    overrideFilterStr = " OR (" + overrideFilterStr + ")";
                else
                    overrideFilterStr = "";

                string paramName = GlobalSettings.SqlVariablePrefix + "HighestTimestamp";

                sql = String.Format("select {0} from {1} where (([{4}] is not null and [{2}] > {3}) {5}) {6}",
                        GetColumnNames(map.SyncEnabledColumns), // {0}
                        map.SourceTableName,                    // {1}
                        map.SourceTimestampColumnName,            // {2}
                        paramName,                              // {3}
                        map.SourceTimestampColumnName,            // {4}
                        extraFilterStr,                          // {5}
                        overrideFilterStr                       // {6}
                    );

            }
            return sql;
        }

         // ((rowupdateddt > '2011-01-01') or (rowupdateddt is null and rowcreateddt > '2011-01-01')) and regdat > '2012-01-01'


        public string SelectBatchDiff(SyncEntity map, int batchSize)
        {
            string sql = String.Format("select * from (select {0} from {1} minus select {2} from {3}) where rownum <= {4}",
                GetColumnNames(map.Columns),
                map.SourceTableName,
                GetColumnNames(map.Columns),
                map.ShadowTableName,
                batchSize
            );
            return sql;
        }

        // http://psoug.org/reference/merge.html
        public string UpsertShadowTable(SyncEntity map)
        {
            string sql = String.Format(@"
                        MERGE INTO {0} b
                USING (
                  SELECT {1}
                  FROM {2}
                  WHERE {3}) e
                ON (b.[{4}] = e.[{5}])
                WHEN MATCHED THEN
                  UPDATE SET {6}
                WHEN NOT MATCHED THEN
                  INSERT ({7})
                  VALUES ({8})
                ",
                map.ShadowTableName, // {0}
                GetColumnNames(map.SyncEnabledColumns), // {1}
                map.SourceTableName, // {2}
                GetColumnNamesPrimaryKeyForWhere(map.SyncEnabledColumns, "", ":", ""), // map.PrimaryKey.ColumnName, // {3}
                map.PrimaryKey.ColumnName, // {4}
                map.PrimaryKey.ColumnName, // {5}
                GetColumnNamesForSet(map.SyncEnabledColumns, "b.", "e."), // {6}
                GetColumnNames(map.SyncEnabledColumns, "b."), // {7}
                GetColumnNames(map.SyncEnabledColumns, "e.") // {8}
            );
            return sql.Replace("\r", "").Replace("\n","");
        }

        public string UpdateShadowTable(SyncEntity map)
        {
            string sql = String.Format("update {0} set {1} where [{2}] = {3}{4}",
                map.ShadowTableName,
                GetColumnNamesForSet(map.SyncEnabledColumns),
                map.PrimaryKey.ColumnName,
                parameterPrefix,
                map.PrimaryKey.ColumnName
            );
            return sql;
        }

        public string DropType(SyncEntity map)
        {
            string sql = String.Format(@"IF TYPE_ID(N'[{0}DataType]') IS NOT NULL begin drop type [{1}DataType] end",
                map.Name,
                map.Name
            );
            return sql;
        }

        public string CreateType(SyncEntity map)
        {
            string sql = String.Format(@"CREATE TYPE [{0}DataType] AS TABLE ({1})",
                map.Name,
                GetColumnDef(map.SyncEnabledColumns)
            );
            return sql;
        }

        public string DropTable(SyncEntity map)
        {
            string sql = String.Format(@"if object_id('[{0}]') is not null
                                        begin
                                            drop table [{1}]
                                        end",
                map.DestinationTableName,
                map.DestinationTableName
            );
            return sql;
        }

        public string CreateTable(SyncEntity map)
        {
            string sql = String.Format(@"
                                        CREATE TABLE [{0}] ({1}, 	[MessageId] [bigint] NOT NULL, [MessageStatus] [int] NOT NULL, [MessageRowId] [bigint] IDENTITY(1,1) NOT NULL, CONSTRAINT [PK_{2}_MessageId] PRIMARY KEY CLUSTERED ([MessageRowId] ASC))",
                map.DestinationTableName,
                GetColumnDef(map.SyncEnabledColumns),
                map.DestinationTableName
            );
            return sql;
        }

        public string DropOracleTable(string tableName)
        {
            string sql = String.Format(@"
                    BEGIN   
                    EXECUTE IMMEDIATE 'drop TABLE {0}'; 
                    EXCEPTION   
                    WHEN OTHERS THEN     
                    IF sqlcode != -0942 
                    THEN RAISE;      
                    END IF; 
                    END; 
                    ",
                           tableName
                       );
            return sql;
        }

        public string DropSQLTable(string tableName) {
            string sql = String.Format(@"if object_id('[{0}]') is not null
                                        begin
                                            drop table [{0}]
                                        end", tableName);
            //string sql = String.Format(@"drop TABLE {0}",tableName);
            return sql;
        }

        public string DropGhostTable(SyncEntity map)
        {
            if(GlobalSettings.IsOracle)
                return DropOracleTable(map.ShadowTableName);
            else
                return DropSQLTable(map.ShadowTableName);
        }

        public string CreateGhostTable(SyncEntity map)
        {
            if (GlobalSettings.IsOracle)
                return CreateOracleGhostTable(map);
            else
                return CreateSQLGhostTable(map);
        }

        private string CreateOracleGhostTable(SyncEntity map) {
            string sql = String.Format(@"CREATE TABLE {0} ({1}, SYNCDAT DATE TABLESPACE USERS",
                map.ShadowTableName,
                GetGhostColumnDef(map.SyncEnabledColumns)
            );
            System.Console.WriteLine(sql);
            return sql;
        }

        private string CreateSQLGhostTable(SyncEntity map) {
            /*string sql = String.Format(@"CREATE TABLE {0} ({1}, SYNCDAT {2})",
                map.ShadowTableName,
                GetGhostColumnDef(map.SyncEnabledColumns),
                GlobalSettings.UseTimestampSyncDat ? "TIMESTAMP" : "DATETIME"
            );*/
            string sql = String.Format(@"CREATE TABLE {0} ({1}, SYNCDAT DATETIME)",
                map.ShadowTableName,
                GetGhostColumnDef(map.SyncEnabledColumns)
            );
            System.Console.WriteLine(sql);
            return sql;
        }

        public string CreateTargetTable(SyncEntity map)
        {
            string sql = String.Format(@"CREATE TABLE {0} ({1}) " +
                "CREATE INDEX index_{0}_id ON {0}(Id)",
                map.TargetTableName,
                GetGhostColumnDef(map.SyncEnabledColumns)
            );

            return sql;
        }

        public string DropInsertSP(SyncEntity map)
        {
            string sql = String.Format(@"if object_id('[Load_{0}]') is not null
                                        begin
                                            drop procedure [Load_{1}]
                                        end",
                map.Name,
                map.Name
            );
            return sql;
        }

        public string DropImportSP(SyncEntity map)
        {
            string sql = String.Format(@"if object_id('[Import_{0}]') is not null
                                        begin
                                            drop procedure [Import_{1}]
                                        end",
                map.Name,
                map.Name
            );
            return sql;
        }

        public string CreateSyncSettingTable()
        {
            string sql = String.Format(@"CREATE TABLE [dbo].[SYNCSETTING](
	            [Id] [int] IDENTITY(1,1) NOT NULL,
	            [Key] [nvarchar](255) NOT NULL,
                [Value] [nvarchar](255) NULL,
                [Description] [nvarchar](255) NULL,
	            [CreatedDt] [DATETIME] NOT NULL,
                [UpdatedDt] [DATETIME] NULL
             CONSTRAINT [PK_SYNCSETTING] PRIMARY KEY CLUSTERED 
            (
	            [ID] ASC
            ))");

            return sql;
        }

        public string CreateSyncInstanceTable()
        {
            string sql = String.Format(@"CREATE TABLE [dbo].[SYNCINSTANCE](
	            [Id] [int] IDENTITY(1,1) NOT NULL,
	            [Name] [varchar](80) NOT NULL,
	            [LastFullSyncStart] [DATETIME] NULL,
                [FullSyncCompleted] [DATETIME] NULL,
	            [RunFullSyncAgain] [bit] NOT NULL,
                [LockedUntil] [DATETIME] NULL,	            
             CONSTRAINT [PK_SYNCINSTANCE] PRIMARY KEY CLUSTERED 
            (
	            [ID] ASC
            ))");

            return sql;
        }

        public string CreateSyncEntityTable()
        {
            string sql = String.Format(@"CREATE TABLE [dbo].[SYNCENTITY](
	            [ID] [int] IDENTITY(1,1) NOT NULL,
                [SyncInstanceId] [int] NOT NULL,
	            [NAME] [nvarchar](MAX) NOT NULL,
	            [SOURCETABLENAME] [nvarchar](MAX) NOT NULL,
	            [SHADOWTABLENAME] [nvarchar](MAX) NOT NULL,
	            [DESTINATIONTABLENAME] [nvarchar](MAX) NOT NULL,
                [TARGETTABLENAME] [nvarchar](MAX) NULL,
	            [CREATE_TIMESTAMP] [datetime] NULL,
	            [UPDATE_TIMESTAMP] [datetime] NULL,
	            [SYNCDAT] [DATETIME] NULL,
	            [DeploySourceDat] [datetime] NULL,
	            [DeployDestinationDat] [datetime] NULL,
	            [ImportDat] [datetime] NULL,
	            [EntityChangedDat] [datetime] NULL,
	            [SORTINDEX] [int] NULL,
	            [SYNCENABLED] [int] NOT NULL,
	            [DESCRIPTION] [varchar](80) NULL,
	            [RESYNCALL] [int] NOT NULL,
	            [ExternalObjectId] [bigint] NULL,
	            [SyncMode] [int] NOT NULL,
	            [SourceInsertedColumnName] [varchar](80) NULL,
	            [SourceUpdatedColumnName] [varchar](80) NULL,
	            [SourceFilter] [varchar](80) NULL,
                [OverrideFilter] [nvarchar](MAX) NULL,
	            [DestinationSyncCompleteSPName] [varchar](80) NULL,
	            [SourceSyncStartSPName] [varchar](80) NULL,
	            [Type] [smallint] NULL,
                [SourceTimestampColumnName] [varchar](80) NULL,
                [HighestTimestamp] [varbinary](8) NULL,
                [KeepDays] [int] NULL,
                [LatestFileSync] [datetime] NULL                
             CONSTRAINT [PK_SYNCENTITY] PRIMARY KEY CLUSTERED 
            (
	            [ID] ASC
            ))");

            return sql;
        }

        public string CreateSyncEntityColumnTable()
        {
            string sql = String.Format(@"CREATE TABLE [dbo].[SYNCENTITYCOLUMN](
	            [ID] [int] IDENTITY(1,1) NOT NULL,
	            [SYNCENTITYID] [int] NOT NULL,
	            [SORTINDEX] [int] NOT NULL,
	            [COLUMNNAME] [varchar](80) NULL,
	            [CSHARPTYPE] [varchar](80) NULL,
	            [PRIMARYKEY] [bit] NOT NULL,
	            [SOURCESQLTYPENAME] [varchar](80) NULL,
	            [SOURCESQLTYPENULLABLE] [int] NOT NULL,
	            [SourceSqlTypeMaxLength] [int] NULL,
	            [SourceSqlColumnId] [int] NULL,
	            [DESTSQLTYPENAME] [varchar](80) NULL,
	            [DESTSQLTYPENULLABLE] [int] NOT NULL,
	            [DestSqlTypeMaxLength] [int] NULL,
                [DestSqlTypePrecision] [int] NULL,
                [DestSqlTypeScale] [int] NULL,
	            [SYNCENABLED] [int] NOT NULL,
             CONSTRAINT [PK_SYNCENTITYCOLUMN] PRIMARY KEY CLUSTERED 
            (
	            [ID] ASC
            ))");

            return sql;
        }

        public string CreateMessageTable()
        {
            string sql = String.Format(@"CREATE TABLE [int_MESSAGE](
	            [Id] [bigint] IDENTITY(1,1) NOT NULL,
	            [MsgType] [nvarchar](40) NOT NULL,
	            [regdat] [datetime] NOT NULL,
	            [status] [int] NOT NULL,
	            [ClientCode] [nvarchar](20) NOT NULL,
	            [MsgCorr] [uniqueidentifier] NULL,
             CONSTRAINT [PK_int_message] PRIMARY KEY CLUSTERED 
            (
	            [Id] ASC
            ))");
            return sql;
        }

        public string CreateLoadMessageSP()
        {
            string sql = String.Format(@"CREATE PROCEDURE [LoadMESSAGE]
	                (
		                @messageType As nvarchar(40), 
		                @clientCode As nvarchar(30), 
		                @messageGuid As uniqueidentifier 
		            )
                    AS
                    Begin
                        Insert Into int_MESSAGE (MsgType, regdat, [status], clientCode, MsgCorr) values (@messageType,GETDATE(),10, @clientCode,@messageGuid);
                        SELECT SCOPE_IDENTITY();

                    End");

            return sql;
        }

        public string CreateIntegrationSP(SyncEntity map)
        {
            string sql = String.Format(@"
                    CREATE PROCEDURE [INT_Import_{0}]
                    (
                        @messageType As nvarchar(40) = NULL, 
		                @clientCode As nvarchar(30) = NULL, 
		                @messageGuid As uniqueidentifier 
                    )
                    AS
                    Begin
	                    UPDATE
		                    {1}
	                    SET 
                            {5}
	                    FROM
		                    {1}
	                    INNER JOIN 
		                    {2} on {6}
	                    INNER JOIN
		                    int_message on int_message.Id = {2}.MessageId
	                    WHERE
		                    int_message.MsgCorr = @messageGuid

	                    INSERT INTO
		                    {1} ({3})
	                    SELECT
		                    {4}
	                    FROM
		                    {2}
	                    INNER JOIN
		                    int_message on int_message.Id = {2}.MessageId
	                    LEFT JOIN
		                    {1} on {6}
	                    WHERE
		                    {1}.Id is null AND
		                    int_message.MsgCorr = @messageGuid
                    End",
                    map.Name, // {0}
                    map.TargetTableName, // {1}
                    map.DestinationTableName, // {2}
                    GetColumnNames(map.SyncEnabledColumns), // {3}
                    GetColumnNames(map.SyncEnabledColumns, map.DestinationTableName + "."), // {4}
                    GetColumnNamesForSet(map.SyncEnabledColumns, map.TargetTableName + ".", map.DestinationTableName + "."), // {5}
                    GetColumnNamesPrimaryKeyForWhere(map.SyncEnabledColumns, map.TargetTableName + ".", map.DestinationTableName + ".", map.TargetTableName + ".Id=" + map.DestinationTableName + ".Id") // {6}
            );
            System.Console.WriteLine(sql);
            return sql;
        }

        public string DropIntegrationSP(SyncEntity map)
        {
            string sql = String.Format(@"if object_id('[INT_Import_{0}]') is not null
                                        begin
                                            drop procedure [Import_{0}]
                                        end",
                map.Name
            );
            return sql;
            
        }

        //, 
		//                @firstMessage As bit, 
        //                @lastMessage As bit

        public string CreateInsertSP(SyncEntity map)
        {
            string sql = String.Format(@"
                    CREATE PROCEDURE [Load_{0}]
	                (
		                @myData As [{1}DataType] Readonly,
		                @msgId As bigint
		            )
                    AS
                    Begin
                        Insert Into {2} ({3},[MessageId],[MessageStatus])
		                Select {4}, @msgId, 0 	
                        From @myData;
                    End",
                    map.Name, // {0}
                    map.Name, // {1}
                    map.DestinationTableName, // {2}
                    GetColumnNames(map.SyncEnabledColumns), // {3}
                    GetColumnNames(map.SyncEnabledColumns) // {4}
            );
            return sql;
        }

        public string CreateImportSP(SyncEntity map)
        {
             // FASTIGHET
            string sql = String.Format(@"
Create PROCEDURE [Import_{0}]
(
    @msgId As bigint
)
AS
Begin
	
	MERGE dbo.Fastighet as dest
		USING dbo.int_FASTIGHET as src 
			ON (dest.Id = src.FASTIGHETID) and src.MessageId = @msgId
		WHEN MATCHED THEN
			UPDATE SET 
				[Id]=src.[FASTIGHETID]
		WHEN NOT MATCHED THEN
			INSERT 
			(
				[Id]
			) VALUES (
				src.[FASTIGHETID]
			)                
		;	

End",
                    map.Name, // {0}
                    map.Name, // {1}
                    map.DestinationTableName, // {2}
                    GetColumnNames(map.SyncEnabledColumns), // {3}
                    GetColumnNames(map.SyncEnabledColumns) // {4}
            );
            return sql;
        }

        public string GetFailedMessages()
        {
            string sql = @"select * from int_Message where status=-1";

            return sql;
        }

        public string GetMessage(long messageId)
        {
            string sql = string.Format(@"select * from int_Message where id = {0}", messageId);

            return sql;
        }

        public string GetFailedMessageRows(IntMessage msg)
        {
            string sql = string.Format(@"select MessageId, MessageStatus, MessageRowId from {0} where MessageStatus=-1 and MessageId={1}", msg.MsgType, msg.Id);

            return sql;
        }

        

    }
}


